﻿using Corina.Datamodel.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class SpecimenService : ServiceBase
    {
        public DataTable GetAllItems(string connectionString, int userId, string code, string name, string deviceSn, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY ts.Id) AS ROW_NUMBER, ts.*, tdk.Name AS Kind, tu.Name AS UserName ");
            builder.Append(" FROM t_specimen AS ts ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_device AS td ON td.DeviceSn = ts.DeviceSn ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_device_kind AS tdk ON tdk.Id = td.DeviceKindId ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_device_sale AS tds ON tds.DeviceSn = td.DeviceSn ");
            builder.Append("LEFT JOIN CORINA_ACCO.dbo.t_user AS tu ON tu.Id = tds.UserId ");
            builder.Append("WHERE 1=1 ");

            builder.Append("AND ts.Name LIKE @Name ");
            paras.Add(new SqlParameter("@Name", string.Format("%{0}%", name)));

            builder.Append("AND ts.DeviceSn LIKE @DeviceSn ");
            paras.Add(new SqlParameter("@DeviceSn", string.Format("%{0}%", deviceSn)));

            if(userId>0)
            {
                builder.Append(" AND tds.UserId = @UserId ");
                paras.Add(new SqlParameter("@UserId", userId));
            }

            if(!string.IsNullOrEmpty(code))
            {
                builder.Append(" AND ts.Code = @Code ");
                paras.Add(new SqlParameter("@Code", code));
            }

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");
            Logis.LogHelper.Instance.Write(string.Format("{0}{1}", qstring, builder), paras.ToArray());

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
            //return DbOperator.GetEntities<SpecimenInfo>(connectionString, new List<string>() { "UserId" }, new List<object>() { userId });
        }

        public bool Insert(string connectionString, SpecimenInfo entity)
        {
            return DbOperator.UpdateEntity(connectionString, entity, Enumerations.EntityOperation.Insert);
        }
    }
}
